﻿/*******************************************************************************
 * Copyright @ 2018 YunSpace.Framework 版权所有
 * Author: WangHui
 * Description: YunSpace 快速开发平台
 * Website：http://www.yunspace.com.cn
*********************************************************************************/
using MySql.Data.MySqlClient;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using YunSpace.Data;
using YunSpace.Domain.ViewModel.OrderFlowManage;
using YunSpace.Domain.IRepository.SiteLibraryManage;
using YunSpace.Domain.IRepository.SiteMgr;
using YunSpace.Domain.ViewModel.Sale;
using YunSpace.Repository.SiteLibraryManage;
using YunSpace.Repository.SiteMgr;
using YunSpace.Core;

namespace YunSpace.Application.OrderFlowManage
{
    public class SaleReportsApp
    {
        /// <summary>
        /// 活动类型
        /// </summary>
        /// <param name="Source"></param>
        /// <returns></returns>
        public List<ReportModel> GetReportByActivyType(string Source)
        {
            string SourceWhere = "";
            if (!String.IsNullOrEmpty(Source))
            {
                SourceWhere = " and t.F_Source=" + Source;
            }
            string sqlStr = $@"select 
	                                t.F_ItemName,t.F_ItemCode,t.TypeName,
	                                IFNULL(t2.num,0) as ALLDemand,
	                                ifnull(t3.num,0) as DemandSource,
	                                ifnull(t4.num,0) as DemandRecommend,
	                                ifnull(t5.num,0) as DemandTakelook,
	                                ifnull(t6.num,0) as DemandBookRecord,
	                                ifnull(t6.money,0) as BookRecordMoney,
	                                ifnull(t7.DemandCash,'') as DemandCahs,
	                                ifnull(t7.ItemName,'') as ItemName,
	                                ifnull(t7.SiteTypeName,'') as SiteTypeName,
	                                ifnull(t7.CloseReason,'') as CloseReason	
                                from 
                                (
	                                select 
			                                t2.F_ItemName,
			                                t2.F_ItemCode,
			                                '一级目录' as TypeName
			                                from Sys_Items t
			                                inner join Sys_ItemsDetail t2
			                                on t.F_Id=t2.F_ItemId
			                                where t.F_EnCode='FirstActivityType'
	                                union all
	                                select 
			                                t2.F_ItemName,
			                                t2.F_ItemCode,
			                                '二级目录' as TypeName
			                                from Sys_Items t
			                                inner join Sys_ItemsDetail t2
			                                on t.F_Id=t2.F_ItemId
			                                where t.F_EnCode='ActivityType'
                                ) t
                                left join
                                (
	                                /*总需求数*/
	                                select 
		                                 t.F_ActivityType,
		                                 count(*) num
	                                from Site_Clients_ProjectByDemand t
                                    where 1=1 {SourceWhere}
	                                group by t.F_ActivityType
                                )t2 on t.F_ItemCode=t2.F_ActivityType
                                left join
                                (
	                                /*需求类型数（按需求来源）*/
	                                select 
		                                 t.F_ActivityType,
		                                 count(*) num
	                                from Site_Clients_ProjectByDemand t 
	                                where 1=1 {SourceWhere}
	                                group by t.F_ActivityType
                                ) t3 on t.F_ItemCode=t3.F_ActivityType
                                left join
                                (
	                                /*成功推荐*/
	                                select
		                                 t.F_ActivityType,
		                                 count(*) num 
	                                from Site_Clients_ProjectByDemand t
	                                inner join Site_Client_Order t2 on t.F_Id=t2.F_DemandId
	                                inner join Site_Client_OrderRecommend t3 on t2.F_Id=t3.F_OrderId
	                                where t3.F_State=1 {SourceWhere}
	                                group by t.F_ActivityType
                                ) t4 on t.F_ItemCode=t4.F_ActivityType
                                left join
                                (
	                                /*成功带看*/
	                                select 
		                                 t.F_ActivityType,
		                                 count(*) num 
	                                from Site_Clients_ProjectByDemand t
	                                inner join Site_Client_Order t2 on t.F_Id=t2.F_DemandId
	                                inner join Site_Client_OrderTakelook t3 on t2.F_Id=t3.F_OrderId
	                                where t3.F_State=1 {SourceWhere}
	                                group by t.F_ActivityType
                                )t5 on t.F_ItemCode=t5.F_ActivityType
                                left join
                                (
	                                /*成功签约*/
	                                select 
		                                 t.F_ActivityType,
		                                 SUM(t3.F_SiteRent+t3.F_MatingRent) as money,
		                                 count(*) num
	                                from Site_Clients_ProjectByDemand t
	                                inner join Site_Client_Order t2 on t.F_Id=t2.F_DemandId
	                                inner join Site_Client_OrderBookRecord t3 on t2.F_Id=t3.F_OrderId
	                                where t3.F_State=1 {SourceWhere}
	                                group by t.F_ActivityType 		
                                )t6 on  t.F_ItemCode=t6.F_ActivityType
                                left join
                                (
                                    /*需求价位范围,行业范围,场地类型范围,关闭理由*/
	                                select 
		                                t.F_ActivityType,
		                                GROUP_CONCAT(distinct t.F_DemandCash) as DemandCash,
		                                GROUP_CONCAT(distinct t2.F_ItemName) as ItemName,
		                                GROUP_CONCAT(distinct t3.F_ItemName) as SiteTypeName,
		                                GROUP_CONCAT(distinct t.F_CloseReason) as CloseReason			
	                                from Site_Clients_ProjectByDemand t
	                                left join 
		                                (
			                                select t2.* from
			                                Sys_ItemsDetail t2 
			                                left join Sys_Items t3 on t2.F_ItemId=t3.F_Id
			                                where t3.F_EnCode='sshy'
		                                ) t2	on t.F_IndustryId=t2.F_ItemCode
	                                left join
		                                (
			                                select t.* from 
			                                Sys_ItemsDetail t 
			                                left join Sys_Items t2 on t.F_ItemId=t2.F_Id
			                                where t2.F_EnCode='SiteType'
		                                ) t3	on t.F_SiteType=t3.F_ItemCode
                                    where 1=1 {SourceWhere}
	                                group by t.F_ActivityType 
                                )t7 on t.F_ItemCode=t7.F_ActivityType ";

            using (var db = new RepositoryBase())
            {
                var dbParams = new MySqlParameter[] {
                };

                var result = db.FindList<ReportModel>(sqlStr, dbParams);
                return result;
            }
        }

        /// <summary>
        /// 行业类型
        /// </summary>
        /// <param name="Source"></param>
        /// <returns></returns>
        public List<ReportModel> GetReportByHangYe(string Source)
        {
            string SourceWhere = "";
            if (!String.IsNullOrEmpty(Source))
            {
                SourceWhere = " and t.F_Source=" + Source;
            }
            string sqlStr = $@"select 
	                                t.F_ItemName,t.F_ItemCode,
	                                IFNULL(t2.num,0) as ALLDemand,
	                                ifnull(t3.num,0) as DemandSource,
	                                ifnull(t4.num,0) as DemandRecommend,
	                                ifnull(t5.num,0) as DemandTakelook,
	                                ifnull(t6.num,0) as DemandBookRecord,
	                                ifnull(t6.money,0) as BookRecordMoney,
	                                ifnull(t7.DemandCash,'') as DemandCahs,
	                                ifnull(t7.ItemName,'') as ActivityName,
	                                ifnull(t7.SiteTypeName,'') as SiteTypeName,
	                                ifnull(t7.CloseReason,'') as CloseReason	
                                from 
                                (
	                                select 
			                                t2.F_ItemName,
			                                t2.F_ItemCode		                               
			                                from Sys_Items t
			                                inner join Sys_ItemsDetail t2
			                                on t.F_Id=t2.F_ItemId
			                                where t.F_EnCode='sshy'	                               
                                ) t
                                left join
                                (
	                                /*总需求数*/
	                                select 
		                                 t.F_IndustryId,
		                                 count(*) num
	                                from Site_Clients_ProjectByDemand t
                                    where 1=1 {SourceWhere}
	                                group by t.F_IndustryId
                                )t2 on t.F_ItemCode=t2.F_IndustryId
                                left join
                                (
	                                /*需求类型数（按需求来源）*/
	                                select 
		                                 t.F_IndustryId,
		                                 count(*) num
	                                from Site_Clients_ProjectByDemand t 
	                                where 1=1 {SourceWhere}
	                                group by t.F_IndustryId
                                ) t3 on t.F_ItemCode=t3.F_IndustryId
                                left join
                                (
	                                /*成功推荐*/
	                                select
		                                 t.F_IndustryId,
		                                 count(*) num 
	                                from Site_Clients_ProjectByDemand t
	                                inner join Site_Client_Order t2 on t.F_Id=t2.F_DemandId
	                                inner join Site_Client_OrderRecommend t3 on t2.F_Id=t3.F_OrderId
	                                where t3.F_State=1 {SourceWhere}
	                                group by t.F_IndustryId
                                ) t4 on t.F_ItemCode=t4.F_IndustryId
                                left join
                                (
	                                /*成功带看*/
	                                select 
		                                 t.F_IndustryId,
		                                 count(*) num 
	                                from Site_Clients_ProjectByDemand t
	                                inner join Site_Client_Order t2 on t.F_Id=t2.F_DemandId
	                                inner join Site_Client_OrderTakelook t3 on t2.F_Id=t3.F_OrderId
	                                where t3.F_State=1 {SourceWhere}
	                                group by t.F_IndustryId
                                )t5 on t.F_ItemCode=t5.F_IndustryId
                                left join
                                (
	                                /*成功签约*/
	                                select 
		                                 t.F_IndustryId,
		                                 SUM(t3.F_SiteRent+t3.F_MatingRent) as money,
		                                 count(*) num
	                                from Site_Clients_ProjectByDemand t
	                                inner join Site_Client_Order t2 on t.F_Id=t2.F_DemandId
	                                inner join Site_Client_OrderBookRecord t3 on t2.F_Id=t3.F_OrderId
	                                where t3.F_State=1 {SourceWhere}
	                                group by t.F_IndustryId 		
                                )t6 on  t.F_ItemCode=t6.F_IndustryId
                                left join
                                (
                                    /*需求价位范围,行业范围,场地类型范围,关闭理由*/
	                                select 
		                                t.F_IndustryId,
		                                GROUP_CONCAT(distinct t.F_DemandCash) as DemandCash,
		                                GROUP_CONCAT(distinct t2.F_ItemName) as ItemName,
		                                GROUP_CONCAT(distinct t3.F_ItemName) as SiteTypeName,
		                                GROUP_CONCAT(distinct t.F_CloseReason) as CloseReason			
	                                from Site_Clients_ProjectByDemand t
	                                left join 
		                                (
			                                select t2.* from
			                                Sys_ItemsDetail t2 
			                                left join Sys_Items t3 on t2.F_ItemId=t3.F_Id
			                                where t3.F_EnCode='FirstActivityType' 
													  or t3.F_EnCode='ActivityType'
		                                ) t2	on t.F_IndustryId=t2.F_ItemCode
	                                left join
		                                (
			                                select t.* from 
			                                Sys_ItemsDetail t 
			                                left join Sys_Items t2 on t.F_ItemId=t2.F_Id
			                                where t2.F_EnCode='SiteType'
		                                ) t3	on t.F_SiteType=t3.F_ItemCode
                                    where 1=1 {SourceWhere}
	                                group by t.F_IndustryId 
                                )t7 on t.F_ItemCode=t7.F_IndustryId";

            using (var db = new RepositoryBase())
            {
                var dbParams = new MySqlParameter[] {
                };

                var result = db.FindList<ReportModel>(sqlStr, dbParams);
                return result;
            }
        }

        /// <summary>
        /// 场地类型
        /// </summary>
        /// <param name="Source"></param>
        /// <returns></returns>
        public List<ReportModel> GetReportByCiteType(string Source)
        {
            string SourceWhere = "";
            if (!String.IsNullOrEmpty(Source))
            {
                SourceWhere = " and t.F_Source=" + Source;
            }
            string sqlStr = $@"select 
	                                t.F_ItemName,t.F_ItemCode,
	                                IFNULL(t2.num,0) as ALLDemand,
	                                ifnull(t3.num,0) as DemandSource,
	                                ifnull(t4.num,0) as DemandRecommend,
	                                ifnull(t5.num,0) as DemandTakelook,
	                                ifnull(t6.num,0) as DemandBookRecord,
	                                ifnull(t6.money,0) as BookRecordMoney,
	                                ifnull(t7.DemandCash,'') as DemandCahs,
	                                ifnull(t7.ActivityName,'') as ActivityName,
	                                ifnull(t7.ItemName,'') as ItemName,
	                                ifnull(t7.CloseReason,'') as CloseReason	
                                from 
                                (
	                                select 
			                                t2.F_ItemName,
			                                t2.F_ItemCode		                               
			                                from Sys_Items t
			                                inner join Sys_ItemsDetail t2
			                                on t.F_Id=t2.F_ItemId
			                                where t.F_EnCode='SiteType'	                               
                                ) t
                                left join
                                (
	                                /*总需求数*/
	                                select 
		                                 t.F_SiteType,
		                                 count(*) num
	                                from Site_Clients_ProjectByDemand t
                                    where 1=1 {SourceWhere}
	                                group by t.F_SiteType
                                )t2 on t.F_ItemCode=t2.F_SiteType
                                left join
                                (
	                                /*需求类型数（按需求来源）*/
	                                select 
		                                 t.F_SiteType,
		                                 count(*) num
	                                from Site_Clients_ProjectByDemand t 
	                                where 1=1 {SourceWhere}
	                                group by t.F_SiteType
                                ) t3 on t.F_ItemCode=t3.F_SiteType
                                left join
                                (
	                                /*成功推荐*/
	                                select
		                                 t.F_SiteType,
		                                 count(*) num 
	                                from Site_Clients_ProjectByDemand t
	                                inner join Site_Client_Order t2 on t.F_Id=t2.F_DemandId
	                                inner join Site_Client_OrderRecommend t3 on t2.F_Id=t3.F_OrderId
	                                where t3.F_State=1 {SourceWhere}
	                                group by t.F_SiteType
                                ) t4 on t.F_ItemCode=t4.F_SiteType
                                left join
                                (
	                                /*成功带看*/
	                                select 
		                                 t.F_SiteType,
		                                 count(*) num 
	                                from Site_Clients_ProjectByDemand t
	                                inner join Site_Client_Order t2 on t.F_Id=t2.F_DemandId
	                                inner join Site_Client_OrderTakelook t3 on t2.F_Id=t3.F_OrderId
	                                where t3.F_State=1 {SourceWhere}
	                                group by t.F_SiteType
                                )t5 on t.F_ItemCode=t5.F_SiteType
                                left join
                                (
	                                /*成功签约*/
	                                select 
		                                 t.F_SiteType,
		                                 SUM(t3.F_SiteRent+t3.F_MatingRent) as money,
		                                 count(*) num
	                                from Site_Clients_ProjectByDemand t
	                                inner join Site_Client_Order t2 on t.F_Id=t2.F_DemandId
	                                inner join Site_Client_OrderBookRecord t3 on t2.F_Id=t3.F_OrderId
	                                where t3.F_State=1 {SourceWhere}
	                                group by t.F_SiteType 		
                                )t6 on  t.F_ItemCode=t6.F_SiteType
                                left join
                                (
                                    /*需求价位范围,行业范围,场地类型范围,关闭理由*/
	                                select 
		                                t.F_SiteType,
		                                GROUP_CONCAT(distinct t.F_DemandCash) as DemandCash,
		                                GROUP_CONCAT(distinct t2.F_ItemName) as ActivityName,
		                                GROUP_CONCAT(distinct t3.F_ItemName) as ItemName,
		                                GROUP_CONCAT(distinct t.F_CloseReason) as CloseReason			
	                                from Site_Clients_ProjectByDemand t
	                                left join 
		                                (
			                                select t2.* from
			                                Sys_ItemsDetail t2 
			                                left join Sys_Items t3 on t2.F_ItemId=t3.F_Id
			                                where t3.F_EnCode='FirstActivityType' 
													  or t3.F_EnCode='ActivityType' 
		                                ) t2	on t.F_ActivityType=t2.F_ItemCode
	                                left join
		                                (
			                                select t.* from 
			                                Sys_ItemsDetail t 
			                                left join Sys_Items t2 on t.F_ItemId=t2.F_Id
			                                where t2.F_EnCode='sshy'
		                                ) t3	on t.F_IndustryId=t3.F_ItemCode
                                    where 1=1  {SourceWhere}
	                                group by t.F_SiteType 
                                )t7 on t.F_ItemCode=t7.F_SiteType";

            using (var db = new RepositoryBase())
            {
                var dbParams = new MySqlParameter[] {
                };

                var result = db.FindList<ReportModel>(sqlStr, dbParams);
                return result;
            }
        }

        /// <summary>
        /// 销售人员类型
        /// </summary>
        /// <param name="Source"></param>
        /// <returns></returns>
        public List<ReportUserModel> GetReportByUser(string queryJson)
        {
            var queryParam = queryJson.ToJObject();
            string sqlWhere = "", sqlWhere2 = "";

            var year = queryParam["Year"].IsEmpty() ? DateTime.Now.Year.ToString() : queryParam["Year"].ToString();
            var month = queryParam["Month"].IsEmpty() || queryParam["Month"].ToString() == "0" ? "0" : queryParam["Month"].ToString();

            DateTime begintime;
            DateTime endtime;
            if (month == "0")
            {
                begintime = new DateTime(int.Parse(year.ToString()), 1, 1);
                endtime = begintime.AddYears(1);
            }
            else
            {
                begintime = new DateTime(int.Parse(year.ToString()), int.Parse(month), 1);
                endtime = begintime.AddMonths(1);
            }


            sqlWhere = sqlWhere + " and unix_timestamp(t.F_CreatorTime)>unix_timestamp('" + begintime.ToString() + "') and unix_timestamp(t.F_CreatorTime)<unix_timestamp('" + endtime.ToString() + "') ";
            sqlWhere2 = sqlWhere2 + " and s.F_Year=" + year;
            sqlWhere2 = sqlWhere2 + (queryParam["Month"].IsEmpty() || queryParam["Month"].ToString() == "0" ? " and s.F_Month!=13" : " and s.F_Month=" + month);

            if (!queryParam["Source"].IsEmpty())
            {
                sqlWhere = sqlWhere + " and t.F_Source=" + queryParam["Source"];
            }

            string sqlStr = $@"select 
		                                t.*,
		                                ifnull(t2.IndicatorValue,0) ProfitValue,/*销售指标*/
		                                ifnull(t3.IndicatorValue,0) IndicatorValue ,/*利润指标*/
		                                ifnull(t4.OrderIncomeCount,0) OrderIncomeCount,/*业绩额*/
		                                ifnull(t5.ALLNum,0) ALLNum,/*总需求数*/
		                                ifnull(t5.FinishNum,0) FinishNum,/*成交需求数*/
		                                ifnull(t5.CloseNum,0) CloseNum,/*关闭需求数*/
		                                ifnull(t5.OtherNum,0) OtherNum,/*储备需求数*/
		                                ifnull(t5.DemandCash,0) DemandCash,/*需求额*/
		                                ifnull(t5.FinishCash,0) FinishCash,/*成交额*/
                                        ifnull(t6.OldOrderNum,0) OldOrderNum/*扔单数*/
                                from 
                                (
	                                select 
			                                u.F_Id,u.F_RealName 
	                                from	Sys_User u
	                                where u.F_DepartmentId in ('5c60c965-8404-452e-8628-b00ee3714e95','01068646-a03a-483e-95fd-41c601552b75','32bc6d4a-3da0-4583-b267-0d751d94bde3','53cca542-a693-4a51-b66e-1ce5daa59b21') 
                                   and u.F_RoleId in ('8d092152-9885-4d53-9fbd-8791191e5098','3b3771c6-e7f4-4fba-aae0-9dd5f0fae3cf','ac595b87-77fe-4e56-8e50-4cea28e11f7c') 
                                   and u.F_EnabledMark=1 and u.F_Id not in('95dbacb1-0d5c-11e8-bc3f-00163e00446b')
                                ) t
                                left join
                                (
	                                SELECT 
			                                IFNULL(SUM(s.F_IndicatorValue), 0) as IndicatorValue,F_UserId as UserId 
                                   FROM	Site_Sales_TargetSetting AS s
                                   WHERE (s.F_DeleteMark IS NULL || s.F_DeleteMark = 0) 
	                                AND s.F_Year ='{year}' AND s.F_Month=13 
                                   group by s.F_UserId
                                )t2 on t.F_Id=t2.UserId
                                left join
                                (
	                                SELECT 
			                                IFNULL(SUM(s.F_IndicatorValue), 0) as IndicatorValue,
			                                F_UserId as UserId 
                                   FROM	Site_Sales_TargetSetting AS s
                                   WHERE (s.F_DeleteMark IS NULL || s.F_DeleteMark = 0) 
	                                {sqlWhere2} 
                                   group by s.F_UserId
                                )t3 on t.F_Id=t3.UserId
                                left join
                                (
		                              select 
				                                sum(su.F_IncomeCash)+sum(su.F_ManagerFee) as OrderIncomeCount,
				                                so.F_FlowUserId  as UserId
		                              from Site_Client_Order so 
                                      left join Site_Clients_ProjectByDemand t on t.F_Id=so.F_DemandId
                                      inner join  Site_Client_OrderUsing su on su.F_OrderId = so.F_Id
                                      inner join
		                                (
				                                select distinct sc.F_No 
				                                from Sys_CheckRecord sc 
				                                where sc.F_ApplyType=6 and sc.F_State=1 
		                                ) scou on scou.F_No = su.F_No 
		                                 where so.F_OrderState=8 {sqlWhere}
		                                 group by so.F_FlowUserId
                                )t4 on t.F_Id=t4.UserId
                                left join
                                (
	                                 SELECT 
                                          t2.F_FlowUserId as UserId,
                                          count(*) as ALLNum,
                                          SUM(if(t2.F_OrderState=8,1,0)) as FinishNum,
                                          SUM(if(t2.F_State=0,1,0)) as CloseNum,
                                          SUM(if(t2.F_OrderState!=8,if(t2.F_State!=0,1,0),0)) as OtherNum,
                                          SUM(F_DemandCash) as DemandCash,
                                          SUM(if(t2.F_OrderState=8,t.F_DemandCash,0)) as FinishCash 
                                    FROM Site_Clients_ProjectByDemand t
                                    INNER JOIN Site_Client_Order t2 on t.F_Id=t2.F_DemandId
                                    WHERE 1=1 {sqlWhere}
                                    GROUP BY t2.F_FlowUserId
                                )t5 on t.F_Id=t5.UserId
                                left join
                                (
	                                 SELECT 
                                          t2.F_OldFlowUserId as UserId,
                                          count(*) as OldOrderNum
                                    FROM Site_Clients_ProjectByDemand t
                                    INNER JOIN Site_Client_Order t2 on t.F_Id=t2.F_DemandId
                                    WHERE 1=1 {sqlWhere}
                                    GROUP BY t2.F_OldFlowUserId
                                )t6 on t.F_Id=t6.UserId
                               order by t.F_Id desc";

            using (var db = new RepositoryBase())
            {
                var result = db.FindList<ReportUserModel>(sqlStr);
                var user = queryParam["User"];
                if (!user.IsEmpty())
                {
                    var a = queryParam["User"].ToString();
                    return result.FindAll(t =>
                    {
                        return a.Contains(t.F_Id.ToString());
                    });
                }
                return result;
            }
        }
    }
}
